package com.witmed.dao.impl;

/**
 * 文章dao层持久化层
 * @author hourenjie
 */

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.witmed.dao.ArticleDao;
import com.witmed.entity.Article;
import com.witmed.entity.Department;
import com.witmed.entity.Doctor;
import com.witmed.entity.Hospital;
import com.witmed.util.ChangeUtils;
import com.witmed.util.DBUtils;
import com.witmed.util.PageInfo;

public class ArticleDaoImpl implements ArticleDao{

	private DBUtils db = new DBUtils();
	
	@Override
	public int insert(Article article) {
		String sql = "insert into Article(article_id,article_title,article_content,article_time,doctor_id) values(null,?,?,?,?)";
		int i = db.update(sql,article.getArticleTitle(),article.getArticleContent(),article.getArticleTime(),article.getDoctor().getDoctorId());
				return i;
	}

	@Override
	public int deleteById(int articleId) {
		return db.update("delete from article where article_id = ?", articleId);
	}

	@Override
	public List<Article> findByArticleTitle(String articleTitle) {
		List<Article> list = new ArrayList<Article>();
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		.append(" A.article_id,")
		.append(" A.article_title,")
		.append(" A.article_content,")
		.append(" A.article_time,")
		.append(" A.doctor_id,")
		.append(" B.doctor_name,")
		.append(" B.department_id,")
		.append(" C.department_name,")
		.append(" C.hospital_id,")
		.append(" D.hospital_name")
		.append(" from article A")
		.append(" inner join doctor B")
		.append(" on A.doctor_id = B.doctor_id")
		.append(" inner join department C")
		.append(" on B.department_id = C.department_id")
		.append(" inner join hospital D")
		.append(" on C.hospital_id= D.hospital_id")
		.append(" where A.article_title like concat('%',?,'%')");
		
	ResultSet rs = db.query(sb.toString(),articleTitle);
	
	try {
		while (rs.next()) {
			Integer articleId    = rs.getInt("article_id");
			articleTitle = rs.getString("article_title");
			String  articleContent = rs.getString("article_content");
			String articleTime = rs.getString("article_time");
			Integer doctorId = rs.getInt("doctor_id");
			String doctorName = rs.getString("doctor_name");
			Integer departmentId = rs.getInt("department_id");
			String departmentName = rs.getString("department_name");
			Integer hospitalId = rs.getInt("hospital_id");
			String hospitalName = rs.getString("hospital_name");
			
			// 构建实体模型
			Hospital hospital = new Hospital(hospitalId, hospitalName);
			Department department = new Department(departmentId, departmentName, hospital);
			Doctor doctor = new Doctor(doctorId, doctorName, null, null, null, null, null,department);
			Article article = new Article(articleId, articleTitle, articleContent, articleTime, doctor);
			
			list.add(article);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return list;
	}

	@Override
	public PageInfo<Article> findByPage(int pageNum, int pageSize) {
		List<Article> data = new ArrayList<Article>();
		
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		.append(" A.article_id,")
		.append(" A.article_title,")
		.append(" A.article_content,")
		.append(" A.article_time,")
		.append(" A.doctor_id,")
		.append(" B.doctor_name,")
		.append(" B.department_id,")
		.append(" C.department_name,")
		.append(" C.hospital_id,")
		.append(" D.hospital_name")
		.append(" from article A")
		.append(" inner join doctor B")
		.append(" on A.doctor_id = B.doctor_id")
		.append(" inner join department C")
		.append(" on B.department_id = C.department_id")
		.append(" inner join hospital D")
		.append(" on C.hospital_id= D.hospital_id")
		.append(" limit ?,?");
		
		ResultSet rs  = db.query(sb.toString(), (pageNum-1)*pageSize, pageSize);
		try {
			while (rs.next()) {
				Integer articleId    = rs.getInt("article_id");
				String articleTitle = rs.getString("article_title");
				String  articleContent = rs.getString("article_content");
				String articleTime = rs.getString("article_time");
				Integer doctorId = rs.getInt("doctor_id");
				String doctorName = rs.getString("doctor_name");
				Integer departmentId = rs.getInt("department_id");
				String departmentName = rs.getString("department_name");
				Integer hospitalId = rs.getInt("hospital_id");
				String hospitalName = rs.getString("hospital_name");
				
				// 构建实体模型
				Hospital hospital = new Hospital(hospitalId, hospitalName);
				Department department = new Department(departmentId, departmentName, hospital);
				Doctor doctor = new Doctor(doctorId, doctorName, null, null, null, null, null,department);
				Article article = new Article(articleId, articleTitle, articleContent, articleTime, doctor);
				
				data.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 2.获取总记录数(未分页之前)
				int total = 0;
				sb = new StringBuffer();
				sb.append("select")
				  .append(" count(*)")
				  .append(" from article A")
				  .append(" inner join doctor B")
				  .append(" on A.doctor_id = B.doctor_id")
				  .append(" inner join department C")
				  .append(" on B.department_id = C.department_id")
				  .append(" inner join hospital D")
				  .append(" on C.hospital_id= D.hospital_id");
				
				rs  = db.query(sb.toString());
				// 遍历结果集
				try {
					while (rs.next()) {
						total  = rs.getInt(1);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				// 构建分页对象
				PageInfo<Article> pageInfo = new PageInfo<Article>(data, pageNum, pageSize, total);
				
		return pageInfo;
	}

	//根据多条件分页查询
	@Override
	public PageInfo<Article> findByPage(int pageNum, int pageSize, Article article) {
		//Integer getArticleId = article.getArticleId();
		String getArticleTitle = article.getArticleTitle();
		String getArticleContent = article.getArticleContent();
		//String getArticleTime = article.getArticleTime();
		//Integer getDoctorId = article.getDoctor().getDoctorId();
		String getDoctorName = article.getDoctor().getDoctorName();
		//Integer getDoctorSex = article.getDoctor().getDoctorSex();
		//Integer getDepartmentId = article.getDoctor().getDepartment().getDepartmentId();
		//String getDepartmentName = article.getDoctor().getDepartment().getDepartmentName();
		//String getHospitalName = article.getDoctor().getDepartment().getHospital().getHospitalName();
		
		List<Article> data = new ArrayList<Article>();
		ChangeUtils cu = new ChangeUtils();
		
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		.append(" A.article_id,")
		.append(" A.article_title,")
		.append(" A.article_content,")
		.append(" A.article_time,")
		.append(" A.doctor_id,")
		.append(" B.doctor_name,")
		.append(" B.department_id,")
		.append(" C.department_name,")
		.append(" C.hospital_id,")
		.append(" D.hospital_name")
		.append(" from article A")
		.append(" inner join doctor B")
		.append(" on A.doctor_id = B.doctor_id")
		.append(" inner join department C")
		.append(" on B.department_id = C.department_id")
		.append(" inner join hospital D")
		.append(" on C.hospital_id= D.hospital_id")
		.append(" where 1=1");
	if (cu.nullToFalse(getArticleTitle)) {
	  sb.append(" and A.article_title like concat('%','"+getArticleTitle+"','%')");	
	    }
	if (cu.nullToFalse(getArticleContent)) {
	  sb.append(" and A.article_content like concat('%','"+getArticleContent+"','%')");
	    }
	if (cu.nullToFalse(getDoctorName)) {
	  sb.append(" and B.doctor_name like concat('%','"+getDoctorName+"','%')");
		}
	/*if (cu.nullToFalse(getDepartmentName)) {
	  sb.append(" and C.department_name like concat('%','"+getDepartmentName+"','%')");
		}	
	if (cu.nullToFalse(getHospitalName)) {
	  sb.append(" and D.hospital_name like concat('%','"+getHospitalName+"','%')");
	    }*/
		
	  sb.append(" limit ?,?");
		
		ResultSet rs  = db.query(sb.toString(),(pageNum-1)*pageSize, pageSize);
		try {
			while (rs.next()) {
				Integer articleId    = rs.getInt("article_id");
				String articleTitle = rs.getString("article_title");
				String  articleContent = rs.getString("article_content");
				String articleTime = rs.getString("article_time");
				Integer doctorId = rs.getInt("doctor_id");
				String doctorName = rs.getString("doctor_name");
				Integer departmentId = rs.getInt("department_id");
				String departmentName = rs.getString("department_name");
				Integer hospitalId = rs.getInt("hospital_id");
				String hospitalName = rs.getString("hospital_name");
				
				// 构建实体模型
				Hospital hospital = new Hospital(hospitalId, hospitalName);
				Department department = new Department(departmentId, departmentName, hospital);
				Doctor doctor = new Doctor(doctorId, doctorName, null, null, null, null, null,department);
				Article article1 = new Article(articleId, articleTitle, articleContent, articleTime, doctor);
				
				data.add(article1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 2.获取总记录数(未分页之前)
				int total = 0;
				sb = new StringBuffer();
				sb.append("select")
				  .append(" count(*)")
				  .append(" from article A")
				  .append(" inner join doctor B")
				  .append(" on A.doctor_id = B.doctor_id")
				  .append(" inner join department C")
				  .append(" on B.department_id = C.department_id")
				  .append(" inner join hospital D")
				  .append(" on C.hospital_id= D.hospital_id")
				  .append(" where A.article_title like concat('%',?,'%')");
				
				rs  = db.query(sb.toString(),getArticleTitle);
				// 遍历结果集
				try {
					while (rs.next()) {
						total  = rs.getInt(1);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				// 构建分页对象
				PageInfo<Article> pageInfo = new PageInfo<Article>(data, pageNum, pageSize, total);
				
		return pageInfo;
	}

	@Override
	public int update(Article article) {
		int n = db.update("update article set article_title = ?,article_content = ? where article_id = ?",
				article.getArticleTitle(), article.getArticleContent(), article.getArticleId());
		return n;
	}

	@Override
	public List<Article> findByAll() {
		List<Article> list = new ArrayList<Article>();
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		.append(" A.article_id,")
		.append(" A.article_title,")
		.append(" A.article_content,")
		.append(" A.article_time,")
		.append(" A.doctor_id,")
		.append(" B.doctor_name,")
		.append(" B.department_id,")
		.append(" C.department_name,")
		.append(" C.hospital_id,")
		.append(" D.hospital_name")
		.append(" from article A")
		.append(" left join doctor B")
		.append(" on A.doctor_id = B.doctor_id")
		.append(" left join department C")
		.append(" on B.department_id = C.department_id")
		.append(" left join hospital D")
		.append(" on C.hospital_id= D.hospital_id");
		
	ResultSet rs = db.query(sb.toString());
	
	try {
		while (rs.next()) {
			Integer articleId    = rs.getInt("article_id");
			String  articleTitle = rs.getString("article_title");
			String  articleContent = rs.getString("article_content");
			String articleTime = rs.getString("article_time");
			Integer doctorId = rs.getInt("doctor_id");
			String doctorName = rs.getString("doctor_name");
			Integer departmentId = rs.getInt("department_id");
			String departmentName = rs.getString("department_name");
			Integer hospitalId = rs.getInt("hospital_id");
			String hospitalName = rs.getString("hospital_name");
			
			// 构建实体模型
			Hospital hospital = new Hospital(hospitalId, hospitalName);
			Department department = new Department(departmentId, departmentName, hospital);
			Doctor doctor = new Doctor(doctorId, doctorName, null, null, null, null, null,department);
			Article article = new Article(articleId, articleTitle, articleContent, articleTime, doctor);
			
			list.add(article);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return list;
	}

	@Override
	public List<Article> findByOrder(Map<String, Object> map) {
		List<Article> list = new ArrayList<Article>();
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		.append(" A.article_id,")
		.append(" A.article_title,")
		.append(" A.article_content,")
		.append(" A.article_time,")
		.append(" A.doctor_id,")
		.append(" B.doctor_name,")
		.append(" B.department_id,")
		.append(" C.department_name,")
		.append(" C.hospital_id,")
		.append(" D.hospital_name")
		.append(" from article A")
		.append(" inner join doctor B")
		.append(" on A.doctor_id = B.doctor_id")
		.append(" inner join department C")
		.append(" on B.department_id = C.department_id")
		.append(" inner join hospital D")
		.append(" on C.hospital_id= D.hospital_id");
		
		String sortBy = (String) map.get("sortBy");
		String ascdesc = (String) map.get("ascdesc");
		int limitCount = Integer.parseInt((String) (map.get("limitCount")));
		
		if ("time".equals(sortBy)) {
			  sb.append(" order by publish_datetime");
			}else if("clickRate".equals(sortBy)) {
			  sb.append(" order by click_count");	
			}
			if ("desc".equals(ascdesc)) {
			  sb.append(" desc");	
			}else if ("asc".equals(ascdesc)) {
			  sb.append(" asc");	
			}
			sb.append(" limit 0,?");
			
			ResultSet rs  = db.query(sb.toString(),limitCount);
			
		try {
			while (rs.next()) {
				Integer articleId    = rs.getInt("article_id");
				String  articleTitle = rs.getString("article_title");
				String  articleContent = rs.getString("article_content");
				String articleTime = rs.getString("article_time");
				Integer doctorId = rs.getInt("doctor_id");
				String doctorName = rs.getString("doctor_name");
				Integer departmentId = rs.getInt("department_id");
				String departmentName = rs.getString("department_name");
				Integer hospitalId = rs.getInt("hospital_id");
				String hospitalName = rs.getString("hospital_name");
					
				// 构建实体模型
				Hospital hospital = new Hospital(hospitalId, hospitalName);
				Department department = new Department(departmentId, departmentName, hospital);
				Doctor doctor = new Doctor(doctorId, doctorName, null, null, null, null, null,department);
				Article article = new Article(articleId, articleTitle, articleContent, articleTime, doctor);
					
				list.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

}
